package cz.muni.fi.pv168.confusinglibrary.database;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.URL;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;

/**
 * Class with auxiliary methods for working with database.
 *
 * @author Petr Adamek (xadamek2@fi.muni.cz)
 * @author Riema
 */
public class DBUtils {

    private static final Logger logger = Logger.getLogger(
            DBUtils.class.getName());

    private DBUtils() {
    }

    /**
     * For making closing connection more well-arranged.
     * Closes connection and logs possible error.
     *
     * @param conn Connection to be closed
     */
    public static void closeQuietly(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                logger.log(Level.WARNING, "Error when closing connection", ex);
            }
        }
    }

    /**
     * Extracting primary key from a ResultSet.
     *
     * @param keys ResultSet with 1 primary key created by database
     * @return id of record
     * @throws SQLException if something goes wrong
     * @throws IllegalArgumentException if parameter contains too much rows or collumns, or no rows or collumns
     */
    public static Long getId(ResultSet keys) throws SQLException {
        if (keys == null) {
            throw new NullPointerException("ResultSet keys empty");
        }
        if (keys.getMetaData().getColumnCount() != 1) {
            throw new IllegalArgumentException("Given ResultSet contains more columns");
        }
        if (keys.next()) {
            Long result = keys.getLong(1);
            if (keys.next()) {
                throw new IllegalArgumentException("Given ResultSet contains more rows");
            }
            return result;
        } else {
            throw new IllegalArgumentException("Given ResultSet contain no rows");
        }
    }

    /**
     * For deleting tables in database
     *
     * @param ds data source with connection to database
     * @throws SQLException
     */
    public static void dropTables(DataSource ds) throws SQLException {
        executeSqlScript(ds, "DropTables.sql");
    }

    /**
     * For creating tables in database
     *
     * @param ds data source with connection to database
     * @throws SQLException
     */
    public static void createTables(DataSource ds) throws SQLException {
        executeSqlScript(ds, "CreateTables.sql");
    }

    /**
     * Method for executing SQL scripts written in a file
     *
     * @param ds data source with connection to database
     * @param scriptName name of the file with SQL command
     * @throws SQLException
     */
    private static void executeSqlScript(DataSource ds, String scriptName) throws SQLException {
        Connection conn = null;
        try {
            conn = ds.getConnection();
            URL url = DBUtils.class.getResource(scriptName);
            for (String sqlStatement : readSqlStatements(url)) {
                if (!sqlStatement.trim().isEmpty()) {
                    conn.prepareStatement(sqlStatement).executeUpdate();
                }
            }
        } finally {
            closeQuietly(conn);
        }
    }

    /**
     * Reading a file containing SQL command
     *
     * @param url of file with SQL command
     * @return String array - every field one command
     */
    private static String[] readSqlStatements(URL url) {
        try {
            char buffer[] = new char[256];
            StringBuilder result = new StringBuilder();
            InputStreamReader reader = new InputStreamReader(url.openStream(), "UTF-8");
            while (true) {
                int count = reader.read(buffer);
                if (count < 0) {
                    break;
                }
                result.append(buffer, 0, count);
            }
            return result.toString().split(";");
        } catch (IOException ex) {
            throw new RuntimeException("Cannot read " + url, ex);
        }
    }

    public static void tryCreateTables(DataSource ds) throws SQLException {
        try {
            createTables(ds);
            logger.warning("Tables created");
        } catch (SQLException ex) {
            if ("X0Y32".equals(ex.getSQLState())) {
                // This code represents "Table/View/... already exists"
                // This code is Derby specific!
                return;
            } else {
                throw ex;
            }
        }
    }
    
    /**
     * 
     */
    public static void createSettingProperties() {
        File f = new File("./src/cz/muni/fi/pv168/confusinglibrary/database/settings.properties");
        if (!f.exists()) {
            try {
                f.createNewFile();
                Properties prop = new Properties();
                prop.setProperty("database.host", "jdbc:derby://localhost:1527/ConfusingLibraryData");
                prop.setProperty("database.username", "Riema");
                prop.setProperty("database.password", "Pa$$w0rd");
                prop.store(new FileOutputStream(f), null);
            } catch (IOException ex) {
                logger.log(Level.SEVERE, null, ex);
            }
        }
    }
}
